package com.css.zfzx.fxpg.modules.preestimate.result.repository.impl;

import com.css.bpm.platform.api.local.PlatformAPI;
import com.css.bpm.platform.org.dept.repository.entity.DeptEntity;
import com.css.bpm.platform.org.division.repository.DivisionRepository;
import com.css.bpm.platform.org.division.repository.entity.DivisionEntity;
import com.css.bpm.platform.org.role.repository.entity.RoleEntity;
import com.css.bpm.platform.utils.PlatformSessionUtils;
import com.css.zfzx.fxpg.common.utils.ServerUtil;
import com.css.zfzx.fxpg.modules.preestimate.result.repository.FxPreestimateResultNativeRepository;
import com.css.zfzx.fxpg.modules.preestimate.result.repository.entity.FxPreestimateResultEntity;
import com.css.zfzx.fxpg.modules.preestimate.result.viewobjects.FxPreestimateResultQueryParams;
import com.css.bpm.platform.utils.PlatformObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.PropertySource;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Repository;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.math.BigInteger;
import java.util.List;

/**
 * @author leon
 * @date 2020-11-19
 */
@Repository
@PropertySource("classpath:platform-config.yml")
public class FxPreestimateResultNativeRepositoryImpl implements FxPreestimateResultNativeRepository {
    @PersistenceContext
    private EntityManager em;
    @Autowired
    private DivisionRepository divisionRepository;

    @Value("${role.superCode}")
    private String superCode;
    @Value("${role.provinceCode}")
    private String provinceCode;
    @Value("${role.cityCode}")
    private String cityCode;
    @Value("${role.areaCode}")
    private String areaCode;

    private static final Logger log = LoggerFactory.getLogger(FxPreestimateResultNativeRepositoryImpl.class);


    @Override
    public Page<FxPreestimateResultEntity> queryFxPreestimateResults(FxPreestimateResultQueryParams queryParams, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select * from fx_preestimate_result ");
        StringBuilder whereSql = new StringBuilder(" where 1=1 ");
        if (!PlatformObjectUtils.isEmpty(queryParams.getWorkspace())) {
            whereSql.append(" and workspace like :workspace");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getYearStart()) &&
                !PlatformObjectUtils.isEmpty(queryParams.getYearEnd())) {
            whereSql.append(" and year >= :yearStart");
            whereSql.append(" and year <= :yearEnd");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getYearStart()) &&
                PlatformObjectUtils.isEmpty(queryParams.getYearEnd())) {
            whereSql.append(" and year >= :yearStart");
        }
        if (PlatformObjectUtils.isEmpty(queryParams.getYearStart()) &&
                !PlatformObjectUtils.isEmpty(queryParams.getYearEnd())) {
            whereSql.append(" and year <= :yearEnd");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getMagnitudeStart()) &&
                !PlatformObjectUtils.isEmpty(queryParams.getMagnitudeEnd())) {
            whereSql.append(" and magnitude >= :magnitudeStart");
            whereSql.append(" and magnitude <= :magnitudeEnd");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getMagnitudeStart()) &&
                PlatformObjectUtils.isEmpty(queryParams.getMagnitudeEnd())) {
            whereSql.append(" and magnitude >= :magnitudeStart");
        }
        if (PlatformObjectUtils.isEmpty(queryParams.getMagnitudeStart()) &&
                !PlatformObjectUtils.isEmpty(queryParams.getMagnitudeEnd())) {
            whereSql.append(" and magnitude <= :magnitudeEnd");
        }

        /*String userId = PlatformSessionUtils.getUserId();
        // 获取当前用户的行政区域
        DeptEntity mainDept = PlatformAPI.getOrgAPI().getUserAPI().getMainDept(userId);
        String divisionId = mainDept.getDivisionId();
        String province = "";
        if (StringUtils.isNotEmpty(divisionId)) {
            DivisionEntity divisionEntity = divisionRepository.findValidDivisionById(divisionId);
            province = divisionEntity.getDivisionName();
            if (StringUtils.isNotEmpty(province)) {
                whereSql.append(" and province like :province");
            }
        }*/
        String userId = PlatformSessionUtils.getUserId();
        List<RoleEntity> roles = PlatformAPI.getOrgAPI().getUserAPI().getRoles(userId);
        // 是否为超级管理员
        boolean containtRole = ServerUtil.isContaintRole(roles, superCode);
        // 是否为省级管理员
        boolean containtRolePro = ServerUtil.isContaintRole(roles, provinceCode);
        // 是否为市级管理员
        boolean containtRoleCity = ServerUtil.isContaintRole(roles, cityCode);
        // 是否为区级管理员
        boolean containtRoleArea = ServerUtil.isContaintRole(roles, areaCode);
        if (!containtRole) {
            // 如果都不是只能查看个人表单
            if (!containtRolePro && !containtRoleCity && !containtRoleArea) {
                whereSql.append(" and create_user =:user");
            }
            // 省级管理员
            if (containtRolePro) {
                whereSql.append(" and province like :divisionName");
            }
            // 市级管理员
            if (containtRoleCity) {
                whereSql.append(" and city like :divisionName");
            }
            // 区级管理员
            if (containtRoleArea) {
                whereSql.append(" and area like :divisionName");
            }
        }

        Query query = this.em.createNativeQuery(sql.append(whereSql).toString(), FxPreestimateResultEntity.class);
        StringBuilder countSql = new StringBuilder("select count(*) from fx_preestimate_result ");
        Query countQuery = this.em.createNativeQuery(countSql.append(whereSql).toString());
        /*if (StringUtils.isNotEmpty(divisionId) && StringUtils.isNotEmpty(province)) {
            query.setParameter("province", "%" + province + "%");
            countQuery.setParameter("province", "%" +province + "%");

        }*/
        if (!containtRole) {
            // 如果都不是只能查看个人表单
            if (!containtRolePro && !containtRoleCity && !containtRoleArea) {
                // 根据用户id查询
                query.setParameter("user", userId);
                countQuery.setParameter("user", userId);
            }
            // 省级管理员 市级管理员 区级管理员
            if (containtRolePro || containtRoleCity || containtRoleArea) {
                // 获取部门地区名称
                String divisionName = ServerUtil.getDivisionEntity(userId).getDivisionName();
                query.setParameter("divisionName", "%" + divisionName + "%");
                countQuery.setParameter("divisionName", "%" + divisionName + "%");
            }
        }

        if (!PlatformObjectUtils.isEmpty(queryParams.getWorkspace())) {
            query.setParameter("workspace", "%" + queryParams.getWorkspace() + "%");
            countQuery.setParameter("workspace", "%" + queryParams.getWorkspace() + "%");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getYearStart())) {
            query.setParameter("yearStart", queryParams.getYearStart());
            countQuery.setParameter("yearStart", queryParams.getYearStart());
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getYearEnd())) {
            query.setParameter("yearEnd", queryParams.getYearEnd());
            countQuery.setParameter("yearEnd", queryParams.getYearEnd());
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getMagnitudeStart())) {
            query.setParameter("magnitudeStart", queryParams.getMagnitudeStart());
            countQuery.setParameter("magnitudeStart", queryParams.getMagnitudeStart());
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getMagnitudeEnd())) {
            query.setParameter("magnitudeEnd", queryParams.getMagnitudeEnd());
            countQuery.setParameter("magnitudeEnd", queryParams.getMagnitudeEnd());
        }
        Pageable pageable = PageRequest.of(curPage - 1, pageSize);
        query.setFirstResult(pageable.getPageNumber() * pageable.getPageSize());
        query.setMaxResults(pageable.getPageSize());
        List<FxPreestimateResultEntity> list = query.getResultList();
        BigInteger count = (BigInteger) countQuery.getSingleResult();
        Page<FxPreestimateResultEntity> fxPreestimateResultPage = new PageImpl<>(list, pageable, count.longValue());
        return fxPreestimateResultPage;
    }

    @Override
    public List<FxPreestimateResultEntity> listFxRiskResults(FxPreestimateResultQueryParams queryParams) {
        StringBuilder sql = new StringBuilder("select * from fx_preestimate_result ");
        StringBuilder whereSql = new StringBuilder(" where 1=1 ");
        if (!PlatformObjectUtils.isEmpty(queryParams.getWorkspace())) {
            whereSql.append(" and workspace like :workspace");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getYearStart()) &&
                !PlatformObjectUtils.isEmpty(queryParams.getYearEnd())) {
            whereSql.append(" and year >= :yearStart");
            whereSql.append(" and year <= :yearEnd");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getYearStart()) &&
                PlatformObjectUtils.isEmpty(queryParams.getYearEnd())) {
            whereSql.append(" and year >= :yearStart");
        }
        if (PlatformObjectUtils.isEmpty(queryParams.getYearStart()) &&
                !PlatformObjectUtils.isEmpty(queryParams.getYearEnd())) {
            whereSql.append(" and year <= :yearEnd");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getMagnitudeStart()) &&
                !PlatformObjectUtils.isEmpty(queryParams.getMagnitudeEnd())) {
            whereSql.append(" and magnitude >= :magnitudeStart");
            whereSql.append(" and magnitude <= :magnitudeEnd");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getMagnitudeStart()) &&
                PlatformObjectUtils.isEmpty(queryParams.getMagnitudeEnd())) {
            whereSql.append(" and magnitude >= :magnitudeStart");
        }
        if (PlatformObjectUtils.isEmpty(queryParams.getMagnitudeStart()) &&
                !PlatformObjectUtils.isEmpty(queryParams.getMagnitudeEnd())) {
            whereSql.append(" and magnitude <= :magnitudeEnd");
        }
        String userId = PlatformSessionUtils.getUserId();
        List<RoleEntity> roles = PlatformAPI.getOrgAPI().getUserAPI().getRoles(userId);
        // 是否为超级管理员
        boolean containtRole = ServerUtil.isContaintRole(roles, superCode);
        // 是否为省级管理员
        boolean containtRolePro = ServerUtil.isContaintRole(roles, provinceCode);
        // 是否为市级管理员
        boolean containtRoleCity = ServerUtil.isContaintRole(roles, cityCode);
        // 是否为区级管理员
        boolean containtRoleArea = ServerUtil.isContaintRole(roles, areaCode);
        if (!containtRole) {
            // 如果都不是只能查看个人表单
            if (!containtRolePro && !containtRoleCity && !containtRoleArea) {
                whereSql.append(" and create_user =:user");
            }
            // 省级管理员
            if (containtRolePro) {
                whereSql.append(" and province like :divisionName");
            }
            // 市级管理员
            if (containtRoleCity) {
                whereSql.append(" and city like :divisionName");
            }
            // 区级管理员
            if (containtRoleArea) {
                whereSql.append(" and area like :divisionName");
            }
        }

        /*String userId = PlatformSessionUtils.getUserId();
        // 获取当前用户的行政区域
        DeptEntity mainDept = PlatformAPI.getOrgAPI().getUserAPI().getMainDept(userId);
        String divisionId = mainDept.getDivisionId();
        String province = "";
        if (StringUtils.isNotEmpty(divisionId)) {
            DivisionEntity divisionEntity = divisionRepository.findValidDivisionById(divisionId);
            province = divisionEntity.getDivisionName();
            if (StringUtils.isNotEmpty(province)) {
                whereSql.append(" and province like :province");
            }
        }*/

        Query query = this.em.createNativeQuery(sql.append(whereSql).toString(), FxPreestimateResultEntity.class);
        /*if (StringUtils.isNotEmpty(divisionId) && StringUtils.isNotEmpty(province)) {
            query.setParameter("province", "%" + province + "%");
        }*/
        if (!containtRole) {
            // 如果都不是只能查看个人表单
            if (!containtRolePro && !containtRoleCity && !containtRoleArea) {
                // 根据用户id查询
                query.setParameter("user", userId);
            }
            // 省级管理员 市级管理员 区级管理员
            if (containtRolePro || containtRoleCity || containtRoleArea) {
                // 获取部门地区名称
                String divisionName = ServerUtil.getDivisionEntity(userId).getDivisionName();
                query.setParameter("divisionName", "%" + divisionName + "%");
            }
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getWorkspace())) {
            query.setParameter("workspace", "%" + queryParams.getWorkspace() + "%");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getYearStart())) {
            query.setParameter("yearStart", queryParams.getYearStart());
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getYearEnd())) {
            query.setParameter("yearEnd", queryParams.getYearEnd());
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getMagnitudeStart())) {
            query.setParameter("magnitudeStart", queryParams.getMagnitudeStart());
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getMagnitudeEnd())) {
            query.setParameter("magnitudeEnd", queryParams.getMagnitudeEnd());
        }
        List<FxPreestimateResultEntity> list = query.getResultList();
        return list;
    }

    @Override
    public List<FxPreestimateResultEntity> getFxPreestimateResults(FxPreestimateResultQueryParams queryParams) {
        StringBuilder sql = new StringBuilder("select * from fx_preestimate_result ");
        StringBuilder whereSql = new StringBuilder(" where 1=1 ");

        if (!PlatformObjectUtils.isEmpty(queryParams.getYear()) &&
                !PlatformObjectUtils.isEmpty(queryParams.getYear())) {
            whereSql.append(" and year = :year");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getMagnitude()) &&
                !PlatformObjectUtils.isEmpty(queryParams.getMagnitude())) {
            whereSql.append(" and magnitude = :magnitude");
        }

        String userId = PlatformSessionUtils.getUserId();
        List<RoleEntity> roles = PlatformAPI.getOrgAPI().getUserAPI().getRoles(userId);
        // 是否为超级管理员
        boolean containtRole = ServerUtil.isContaintRole(roles, superCode);
        // 是否为省级管理员
        boolean containtRolePro = ServerUtil.isContaintRole(roles, provinceCode);
        // 是否为市级管理员
        boolean containtRoleCity = ServerUtil.isContaintRole(roles, cityCode);
        // 是否为区级管理员
        boolean containtRoleArea = ServerUtil.isContaintRole(roles, areaCode);
        if (!containtRole) {
            // 如果都不是只能查看个人表单
            if (!containtRolePro && !containtRoleCity && !containtRoleArea) {
                whereSql.append(" and create_user =:user");
            }
            // 省级管理员
            if (containtRolePro) {
                whereSql.append(" and province like :divisionName");
            }
            // 市级管理员
            if (containtRoleCity) {
                whereSql.append(" and city like :divisionName");
            }
            // 区级管理员
            if (containtRoleArea) {
                whereSql.append(" and area like :divisionName");
            }
        }

        Query query = this.em.createNativeQuery(sql.append(whereSql).toString(), FxPreestimateResultEntity.class);
        if (!containtRole) {
            // 如果都不是只能查看个人表单
            if (!containtRolePro && !containtRoleCity && !containtRoleArea) {
                // 根据用户id查询
                query.setParameter("user", userId);
            }
            // 省级管理员 市级管理员 区级管理员
            if (containtRolePro || containtRoleCity || containtRoleArea) {
                // 获取部门地区名称
                String divisionName = ServerUtil.getDivisionEntity(userId).getDivisionName();
                query.setParameter("divisionName", "%" + divisionName + "%");
            }
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getMagnitude())) {
            query.setParameter("magnitude", queryParams.getMagnitude());
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getYear())) {
            query.setParameter("year", queryParams.getYear());
        }

        List<FxPreestimateResultEntity> list = query.getResultList();
        return list;
    }

    @Override
    public Page<FxPreestimateResultEntity> querySpatialLocation(FxPreestimateResultQueryParams queryParams, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select * from fx_preestimate_result ");
        StringBuilder whereSql = new StringBuilder(" where 1=1 ");
        if (!PlatformObjectUtils.isEmpty(queryParams.getYear()) &&
                !PlatformObjectUtils.isEmpty(queryParams.getYear())) {
            whereSql.append(" and year = :year");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getMagnitude()) &&
                !PlatformObjectUtils.isEmpty(queryParams.getMagnitude())) {
            whereSql.append(" and magnitude = :magnitude");
        }
        String userId = PlatformSessionUtils.getUserId();
        List<RoleEntity> roles = PlatformAPI.getOrgAPI().getUserAPI().getRoles(userId);
        // 是否为超级管理员
        boolean containtRole = ServerUtil.isContaintRole(roles, superCode);
        // 是否为省级管理员
        boolean containtRolePro = ServerUtil.isContaintRole(roles, provinceCode);
        // 是否为市级管理员
        boolean containtRoleCity = ServerUtil.isContaintRole(roles, cityCode);
        // 是否为区级管理员
        boolean containtRoleArea = ServerUtil.isContaintRole(roles, areaCode);
        if (!containtRole) {
            // 如果都不是只能查看个人表单
            if (!containtRolePro && !containtRoleCity && !containtRoleArea) {
                whereSql.append(" and create_user =:user");
            }
            // 省级管理员
            if (containtRolePro) {
                whereSql.append(" and province like :divisionName");
            }
            // 市级管理员
            if (containtRoleCity) {
                whereSql.append(" and city like :divisionName");
            }
            // 区级管理员
            if (containtRoleArea) {
                whereSql.append(" and area like :divisionName");
            }
        }

        Query query = this.em.createNativeQuery(sql.append(whereSql).toString(), FxPreestimateResultEntity.class);
        StringBuilder countSql = new StringBuilder("select count(*) from fx_preestimate_result ");
        Query countQuery = this.em.createNativeQuery(countSql.append(whereSql).toString());

        if (!containtRole) {
            // 如果都不是只能查看个人表单
            if (!containtRolePro && !containtRoleCity && !containtRoleArea) {
                // 根据用户id查询
                query.setParameter("user", userId);
                countQuery.setParameter("user", userId);
            }
            // 省级管理员 市级管理员 区级管理员
            if (containtRolePro || containtRoleCity || containtRoleArea) {
                // 获取部门地区名称
                String divisionName = ServerUtil.getDivisionEntity(userId).getDivisionName();
                query.setParameter("divisionName", "%" + divisionName + "%");
                countQuery.setParameter("divisionName", "%" + divisionName + "%");
            }
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getYear())) {
            query.setParameter("year", queryParams.getYear());
            countQuery.setParameter("year", queryParams.getYear());
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getMagnitude())) {
            query.setParameter("magnitude", queryParams.getMagnitude());
            countQuery.setParameter("magnitude", queryParams.getMagnitude());
        }
        Pageable pageable = PageRequest.of(curPage - 1, pageSize);
        query.setFirstResult(pageable.getPageNumber() * pageable.getPageSize());
        query.setMaxResults(pageable.getPageSize());
        List<FxPreestimateResultEntity> list = query.getResultList();
        BigInteger count = (BigInteger) countQuery.getSingleResult();
        Page<FxPreestimateResultEntity> fxPreestimateResultPage = new PageImpl<>(list, pageable, count.longValue());
        return fxPreestimateResultPage;
    }

    @Override
    public Page<FxPreestimateResultEntity> querySpatialLocationByBoundary(FxPreestimateResultQueryParams queryParams, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select * from fx_preestimate_result ");
        StringBuilder whereSql = new StringBuilder(" where 1=1 ");
        if (!PlatformObjectUtils.isEmpty(queryParams.getQueryBoundary())) {
            whereSql.append(" and Contains(GeomFromText('POLYGON ((" + queryParams.getQueryBoundary() + "))'), GeomFromText(CONCAT('POINT(', longitude, ' ', latitude, ')')))");
        }
        String userId = PlatformSessionUtils.getUserId();
        List<RoleEntity> roles = PlatformAPI.getOrgAPI().getUserAPI().getRoles(userId);
        // 是否为超级管理员
        boolean containtRole = ServerUtil.isContaintRole(roles, superCode);
        // 是否为省级管理员
        boolean containtRolePro = ServerUtil.isContaintRole(roles, provinceCode);
        // 是否为市级管理员
        boolean containtRoleCity = ServerUtil.isContaintRole(roles, cityCode);
        // 是否为区级管理员
        boolean containtRoleArea = ServerUtil.isContaintRole(roles, areaCode);
        if (!containtRole) {
            // 如果都不是只能查看个人表单
            if (!containtRolePro && !containtRoleCity && !containtRoleArea) {
                whereSql.append(" and create_user =:user");
            }
            // 省级管理员
            if (containtRolePro) {
                whereSql.append(" and province like :divisionName");
            }
            // 市级管理员
            if (containtRoleCity) {
                whereSql.append(" and city like :divisionName");
            }
            // 区级管理员
            if (containtRoleArea) {
                whereSql.append(" and area like :divisionName");
            }
        }

        Query query = this.em.createNativeQuery(sql.append(whereSql).toString(), FxPreestimateResultEntity.class);
        StringBuilder countSql = new StringBuilder("select count(*) from fx_preestimate_result ");
        Query countQuery = this.em.createNativeQuery(countSql.append(whereSql).toString());
        /*
        if(!PlatformObjectUtils.isEmpty(queryParams.getQueryBoundary())) {
            query.setParameter("queryBoundary", queryParams.getQueryBoundary());
            countQuery.setParameter("queryBoundary", queryParams.getQueryBoundary());
        }*/
        if (!containtRole) {
            // 如果都不是只能查看个人表单
            if (!containtRolePro && !containtRoleCity && !containtRoleArea) {
                // 根据用户id查询
                query.setParameter("user", userId);
                countQuery.setParameter("user", userId);
            }
            // 省级管理员 市级管理员 区级管理员
            if (containtRolePro || containtRoleCity || containtRoleArea) {
                // 获取部门地区名称
                String divisionName = ServerUtil.getDivisionEntity(userId).getDivisionName();
                query.setParameter("divisionName", "%" + divisionName + "%");
                countQuery.setParameter("divisionName", "%" + divisionName + "%");
            }
        }
        Pageable pageable = PageRequest.of(curPage - 1, pageSize);
        query.setFirstResult(pageable.getPageNumber() * pageable.getPageSize());
        query.setMaxResults(pageable.getPageSize());
        List<FxPreestimateResultEntity> list = query.getResultList();
        BigInteger count = (BigInteger) countQuery.getSingleResult();
        Page<FxPreestimateResultEntity> fxPreestimateResultPage = new PageImpl<>(list, pageable, count.longValue());
        return fxPreestimateResultPage;
    }
}
